{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd,xlwings as xw"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "wb1=xw.Book(r'J:\\王振洋资料\\2.商贸分公司资料\\t+基础数据金水分.xlsx')\n",
    "仓库档案=wb1.sheets('仓库档案').range('a1').expand().options(pd.DataFrame).value.reset_index()\n",
    "存货档案=wb1.sheets('存货档案').range('a1').expand('table').options(pd.DataFrame).value.reset_index()\n",
    "存货档案=存货档案.reset_index().loc[:,['存货编码','存货名称','计量单位']]\n",
    "仓库档案['key']=1\n",
    "存货档案['key']=1\n",
    "#底稿格式设置为文本#"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "wb暂估底稿=xw.Book(r'j:\\王振洋资料\\2.商贸分公司资料\\9月商贸分公司资料\\3.暂估入库\\9月暂估入库底稿.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "合并=pd.merge(仓库档案,存货档案,on='key').drop_duplicates(subset=['仓库名称','存货名称'])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "wb暂估底稿.sheets[0].range('a1').value=合并"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "合并.to_clipboard()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 收发存汇总表的处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "收发存汇总表=pd.read_excel(r'j:\\王振洋资料\\2.商贸分公司资料\\9月商贸分公司资料\\3.暂估入库\\暂估前收发存汇总表.xlsx',skiprows=5)  # 这个就是导出来收发存汇总表不用动，修改一下路径就可以。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "收发存汇总表=收发存汇总表.transpose() # 转置，以筛选符合条件的列\n",
    "收发存汇总表=收发存汇总表[(收发存汇总表[1]!='平均单价')  & (收发存汇总表[1]!='金额') ]  #筛选出来不等于平均单价 和金额的，最后剩下的就只有数量啦\n",
    "收发存汇总表=收发存汇总表.transpose() #转置回来\n",
    "收发存汇总表.columns=收发存汇总表.loc[0]  # 将第一行提升为标题\n",
    "收发存汇总表=收发存汇总表.iloc[2:] #从第二行开始，之前的0,1两行就不要了\n",
    "收发存汇总表['仓库']=收发存汇总表['仓库'].fillna(method='ffill') #把仓库列填充一下\n",
    "result=收发存汇总表.query('存货编码.notna()').loc[:,['仓库','存货','期初结存','本期入库','本期出库']] # 最后筛选出符合条件的列。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 合并收发存汇总表的数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "merge = pd.merge(合并,result,left_on=['仓库名称','存货名称'],right_on=['仓库','存货'],how='left').loc[:,['仓库编码','仓库名称','存货编码','存货名称','计量单位','期初结存','本期入库','本期出库']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "merge.to_clipboard()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 合并“上月暂估入库数”"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "#1.导入上月暂估入库数到df\n",
    "上月暂估入库数=pd.read_excel(r'j:\\王振洋资料\\2.商贸分公司资料\\9月商贸分公司资料\\3.暂估入库\\红冲8月暂估入库.xlsx',header=0)\n",
    "上月暂估入库数=上月暂估入库数.loc[:,['仓库','存货名称','*实收数量']]\n",
    "上月暂估入库数.rename(columns={'*实收数量':'上月暂估入库'},inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "merge=pd.merge(merge,上月暂估入库数,left_on=['仓库名称','存货名称'],right_on=['仓库','存货名称'],how='left').loc[:,\n",
    "    ['仓库编码','仓库名称','存货编码','存货名称','计量单位','期初结存','本期入库','本期出库','上月暂估入库']\n",
    "    ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "merge.to_clipboard()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 合并“本月销售数”\n",
    "\n",
    "这个可以是从"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "# wb_跑男商城=xw.Book(r'K:\\BaiduSyncdisk\\王振洋资料\\1月商贸分公司资料\\4.本月销售-跑男商城\\2.商城订单导出（商贸分物料）.xlsx')\n",
    "wb_线下跑腿=xw.Book(r'j:\\王振洋资料\\2.商贸分公司资料\\9月商贸分公司资料\\2.本月销售\\9月跑腿物料销售明细.xlsx')\n",
    "#wb_线下家政=xw.Book(r'j:\\王振洋资料\\2.商贸分公司资料\\8月商贸分公司资料\\本月销售\\8月代理商家政物料销售.xlsx')\n",
    "#wb_其他跑腿=xw.Book(r'J:\\王振洋资料\\2.商贸分公司资料\\6月商贸分公司资料\\23年11月采购对应销售确认\\许昌榛果204824.4\\许昌榛果跑腿物料116404_代驾3622元.xlsx')\n",
    "#wb_其他家政=xw.Book(r'J:\\王振洋资料\\2.商贸分公司资料\\6月商贸分公司资料\\23年11月采购对应销售确认\\许昌榛果204824.4\\许昌榛果家政物料141242元.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''\n",
    "本月销售_跑男商城=wb_跑男商城.sheets('销售出库单').range('a2').expand().options(pd.DataFrame).value\n",
    "本月销售_跑男商城=(\n",
    "    本月销售_跑男商城.loc[:,['仓库','存货名称','*数量']]\n",
    "    .rename(columns={'*数量':'本月销售'})\n",
    "    .groupby(by=['仓库','存货名称']).sum().reset_index()\n",
    ")\n",
    "'''\n",
    "\n",
    "\n",
    "本月销售_线下跑腿=wb_线下跑腿.sheets('销售出库单').range('a2').expand().options(pd.DataFrame).value\n",
    "本月销售_线下跑腿=(\n",
    "    本月销售_线下跑腿.loc[:,['仓库','存货名称','*数量']]\n",
    "    .rename(columns={'*数量':'本月销售'})\n",
    "    .groupby(by=['仓库','存货名称']).sum().reset_index()\n",
    ")\n",
    "'''\n",
    "本月销售_线下家政=wb_线下家政.sheets('销售出库单').range('a2').expand().options(pd.DataFrame).value\n",
    "本月销售_线下家政=(\n",
    "    本月销售_线下家政.loc[:,['仓库','存货名称','*数量']]\n",
    "    .rename(columns={'*数量':'本月销售'})\n",
    "    .groupby(by=['仓库','存货名称']).sum().reset_index()\n",
    ")\n",
    "\n",
    "\n",
    "本月销售_其他跑腿=wb_其他跑腿.sheets('销售出库单').range('a2').expand().options(pd.DataFrame).value\n",
    "本月销售_其他跑腿=(\n",
    "    本月销售_其他跑腿.loc[:,['仓库','存货名称','*数量']]\n",
    "    .rename(columns={'*数量':'本月销售'})\n",
    "    .groupby(by=['仓库','存货名称']).sum().reset_index()\n",
    ")\n",
    "\n",
    "本月销售_其他家政=wb_其他家政.sheets('销售出库单').range('a2').expand().options(pd.DataFrame).value\n",
    "本月销售_其他家政=(\n",
    "    本月销售_其他家政.loc[:,['仓库','存货名称','*数量']]\n",
    "    .rename(columns={'*数量':'本月销售'})\n",
    "    .groupby(by=['仓库','存货名称']).sum().reset_index()\n",
    ")\n",
    "\n",
    "# 本月销售_跑男商城, 这个以后估计是用不到了\n",
    "'''\n",
    "合并2=pd.concat([本月销售_线下跑腿]).groupby(by=['仓库','存货名称']).sum().reset_index()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "合并2.to_clipboard()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge2\n",
    "merge2=pd.merge(merge,合并2, left_on=['仓库名称','存货名称'],right_on=['仓库','存货名称'],how='left')\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [],
   "source": [
    "merge2.to_clipboard()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 计算确定 应暂估入库数\n",
    "在“确定应暂估数” 的 表中 添加公式，计算应暂估数"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 将数据从暂估入库数据源 写入 采购入库单 模板"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [],
   "source": [
    "sheetname = wb暂估底稿.sheet_names\n",
    "\n",
    "采购入库单p=r'j:\\王振洋资料\\2.商贸分公司资料\\商贸分导入模板\\采购入库单.xlsx'\n",
    "\n",
    "\n",
    "if '采购入库单' not in sheetname:   \n",
    "    出库单=xw.Book(采购入库单p)\n",
    "    出库单.sheets['采购入库单'].copy(after=wb暂估底稿.sheets[wb暂估底稿.sheets.count-1])\n",
    "    出库单.close()\n",
    "   \n",
    "else:\n",
    "    None\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [],
   "source": [
    "wb暂估底稿=xw.Book(r'j:\\王振洋资料\\2.商贸分公司资料\\9月商贸分公司资料\\3.暂估入库\\9月暂估入库底稿.xlsx')\n",
    "数据源=wb暂估底稿.sheets('暂估入库').range('a1').expand().options(pd.DataFrame).value\n",
    "数据源=数据源.reset_index()\n",
    "ws2=wb暂估底稿.sheets('采购入库单')\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "ws2.range('a5:aa10000').clear_contents()\n",
    "ws2.range('g3:h3').value=数据源.loc[:,['供应商编码','供应商']].values\n",
    "ws2.range('m3:o3').value=数据源.loc[:,['备注','仓库编码','仓库名称']].values\n",
    "ws2.range('r3:s3').value=数据源.loc[:,['存货编码','存货名称']].values\n",
    "ws2.range('u3:v3').value=数据源.loc[:,['计量单位','本月应暂估入库数']].values\n",
    "ws2.range('i3:j3').value=数据源.loc[:,['部门编码','部门']].values\n",
    "ws2.range('z3').options(transpose=True).value=数据源['合计金额'].values\n",
    "\n",
    "ws2.range('a3:A4').value='2024-2-29'\n",
    "ws2.range('b3:b4').value=1\n",
    "ws2.range('c3').value='01'\n",
    "ws2.range('d3:d4').value='普通采购'\n",
    "ws2.range('e3:e4').value='101'\n",
    "ws2.range('f3:f4').value='采购入库'\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
